<?php
/*
 *  Update active_loans with delay and penalties acumulated on each active loan.
 *  Note that active_loans only deals with the next expected payment for each active loan,
 *  for loans with more than 1 due payment, only the older due payment values are considered.
 */

require_once 'z.php';

$date = date('Y-m-d');
$datetime = date('Y-m-d H:i:s');
$delay = $app_config->$argv[1]->param->write_offs->delay;
$hits = $app_config->$argv[1]->param->write_offs->hits;

$db = Zend_Db_Table_Abstract::getDefaultAdapter();
$db->beginTransaction();

try {
/*
 * insert loan_write_offs
 */
$db->query('
insert 
    loan_write_offs
    (LoanId, Amount, Principal, LoanInsurance, HealthInsurance, AdministrativeFee, Interest, Penalties, LastEditDate, LastEditUserId)
select 
    a.LoanId,
    (a.BalancePrincipal + a.BalanceLoanInsurance + a.BalanceHealthInsurance + a.BalanceAdministrativeFee + a.Interest + a.Penalty) Amount,
    a.BalancePrincipal Principal,
    a.BalanceLoanInsurance LoanInsurance,
    a.BalanceHealthInsurance HealthInsurance,
    a.BalanceAdministrativeFee AdministrativeFee,
    a.Interest,
    a.Penalty Penalties,
    a.Date LastEditDate,
    1 LastEditUserId
from 
    arrears a 
where 
    a.Date = ? and (a.Delay >= ? or a.Hits >= ?)', array($date,$delay,$hits));

/*
 * insert loan_status_changes
 */
$db->query('
insert
    loan_status_changes (LoanId, PreviousStatus, Status, Date, Datetime, UserId, Comments)
select 
    a.LoanId,
    "Disbursed" PreviousStatus,
    "Written-Off" Status,
    a.Date,
    ? Datetime,
    1 UserId,
    "" Comments
from 
    arrears a 
where 
    a.Date = ? and (a.Delay >= ? or a.Hits >= ?)', array($datetime,$date,$delay,$hits));

/*
 * update loans
 */
$db->query('
update 
    loans 
inner join 
    arrears on loans.LoanId = arrears.LoanId
set 
    loans.Status = ?,
    loans.LastEditDate = ?,
    loans.LastEditUserId = 1
where 
    arrears.Date = ? and (arrears.Delay >= ? or arrears.Hits >= ?)', array('Written-Off',$date,$date,$delay,$hits));

/*
 * delete active_loans
 */
$db->query('delete from active_loans where LoanId in (select LoanId from arrears where Date = ? and (Delay >= ? or Hits >= ?))', array($date,$delay,$hits));

/*
 * delete arrears
 */
$db->query('delete from arrears where Date = ? and (Delay >= ? or Hits >= ?)', array($date,$delay,$hits));

} catch (Zend_Exception $e) {
    $db->rollBack();
    echo $e->getMessage();
    exit;
}
$db->commit();

